from django.test import TestCase

# Create your tests here.


import os

if __name__ == '__main__':
    os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'mjapp01.settings')
    import django

    django.setup()
    from app02 import models

    # 1.创建数据
    # user_obj = models.User.objects.create(name='hehehehe', age=22)
    # user_obj = models.User.objects.create(name='grhthkkkhth', age=34)
    # user_obj = models.User.objects.create(name='cgrhththt', age=44)
    # user_obj = models.User.objects.create(name='mjgrgrgr', age=56)
    # user_obj = models.User.objects.create(name='cbtafaf', age=95)
    # print(user_obj)
    # 2.查询数据
    # res = models.User.objects.filter()       # 列表套数据对象
    # print(res[1])    # 只支持正数索引
    # print(res.first())  # 拿第一个数据 推荐使用first方法获取数据对象 查询不存在也不会报错
    # res = models.User.objects.all()  # all
    # 3.指定字段数据
    # res = models.User.objects.values('name')
    # print(res)   # values结果 可以看出列表套字典
    # res = models.User.objects.values_list('name', 'age')
    # print(res)   # vailues_list 可以看出列表套元组
    # 4.按照指定字段排序
    # res = models.User.objects.order_by('age')   # 默认升序
    # res = models.User.objects.order_by('-age')  # 降序，字段前加负号
    # print(res)
    # 5.统计数据的个数
    # res = models.User.objects.count()
    # print(res)
    # 6.按照所给的条件取反
    # res = models.User.objects.exclude(name='ckkkkkk')
    # print(res)
    # 7.颠倒前后顺序
    # res1 = models.User.objects.all()
    # res2 = models.User.objects.reverse()
    # print(res1, res2)
    # res1 = models.User.objects.order_by('name')
    # res2 = models.User.objects.order_by('age').reverse()
    # print(res1, res2)
    # 8.判断结果集是否含有数据
    # res = models.User.objects.all().exists()
    # print(res)
    # res = models.User.objects.filter(id=200).exists()
    # print(res)
    # 9.去重(一摸一样的数据)
    # res = models.User.objects.values('name','age').distinct()
    # print(res)

    # 查询年龄小于25的数据
    # res = models.User.objects.filter(age__lt=25)
    # print(res)
    #
    # # 查询年龄小于等于25的数据
    # res1 = models.User.objects.filter(age__lte=25)
    # print(res1)

    # 查询年龄大于等于25的数据
    # res = models.User.objects.filter(age__gte=25)
    # print(res)

    # 查询姓名包含ckk的
    # res = models.User.objects.filter(name__contains='ckk')
    # print(res)
    # res = models.User.objects.filter(name__icontains='CKK')  # 忽略大小写
    # print(res)

    # 查询年龄是18或者28或者38的人
    # res = models.User.objects.filter(age__in=(18, 28, 38))
    # print(res)
    # 查询年龄在20到30岁之间
    # res = models.User.objects.filter(age__range=(20,50))
    # print(res)

    # 查询日期是2023的数据
    # res = models.User.objects.filter(join_time__year=2023)
    # print(res)
    #
    # res = models.User.objects.filter(join_time__month=6)
    # print(res)

    # 外键关系字段
    from app01 import models

    # models.Book.objects.create(title='水浒传', price=444.44, publish_id=1)   # 填写关联数据的主键值

    # publish_obj = models.Publish.objects.filter(pk=3).first()
    # models.Book.objects.create(title='西游记', price=888.77, publish=publish_obj)  # 关联数据的数据对象
    # models.Book.objects.filter(pk=1).update(publish_id=2)
    # publish_obj = models.Publish.objects.filter(pk=1).first()
    # models.Book.objects.filter(pk=2).update(publish=publish_obj)

    # 多对多
    # book_obj = models.Book.objects.filter(pk=1).first()
    # book_obj.authors.add(1)  # 直接在书跟作者的第三张关系表中添加数据
    # book_obj.authors.add(2, 3) # 一次传多个

    # author_obj1 = models.Author.objects.filter(pk=1).first()
    # author_obj2 = models.Author.objects.filter(pk=2).first()
    # author_obj3 = models.Author.objects.filter(pk=3).first()
    # book_obj.authors.add(author_obj1)
    # book_obj.authors.add(author_obj2, author_obj3)
    # book_obj.authors.set([1, 3])
    # book_obj.authors.set([author_obj2,author_obj3])  # 支持主键值或关联对象

    # book_obj.authors.remove(2)
    # book_obj.authors.remove(2, 3)
    # book_obj.authors.remove(author_obj2, author_obj3)
    # book_obj.authors.clear()  # 一次性清空当前书籍对象与作者的所有绑定关系

    # 1.查询主键为1的书籍对应出版社名称
    # book_obj = models.Book.objects.filter(pk=1).first()
    # print(book_obj.publish)

    # # 2.查询主键为1的书籍对应的作者名
    # book_obj = models.Book.objects.filter(pk=1).first()
    # print(book_obj.authors.all())
    # 3.查询ckk的电话号码
    # author_obj = models.Author.objects.filter(name='ckk').first()
    # print(author_obj.author_detail.phone)
    # print(author_obj.author_detail.addr)

    # 4.查询东方出版社出版过的书籍名称
    # publish_obj = models.Publish.objects.filter(title='哈哈哈出版社').first()
    # print(publish_obj.book_set.all())  # 结果集可能有多个 需要加all

    # 5.查询ckk写过的书籍名称
    # author_obj = models.Author.objects.filter(name='ckk').first()
    # print(author_obj.book_set.all())

    # 6.查询120电话作者姓名
    # author_detail_obj = models.AuthorDetail.objects.filter(phone=120).first()
    # print(author_detail_obj.author)  # 1对1 结果集，不需要_set.all()

    # 双下划线的跨表查询 连表操作
    # 1.查询主键为1的书籍对应的出版社名称
    # res = models.Book.objects.filter(pk=1).values('publish__title')  # values、 values_list 括号中同样支持正方向操作
    # print(res)
    # res1 = models.Book.objects.filter(pk=1).values('publish__title', 'publish__email', 'title')
    # print(res1)

    # 2.查询主键为1的书籍对应的作者名
    # res = models.Book.objects.filter(pk=1).values('authors__name', 'title')
    # print(res)

    # 3.查询ckk的电话号码
    # res = models.Author.objects.filter(name='ckk').values('author_detail__phone')
    # print(res)

    # 4.查询哈哈哈出版社出版过的书籍名称
    # res = models.Publish.objects.filter(title='哈哈哈出版社').values('book__title', 'book__price')
    # print(res)

    # 5.查询ckk写过的书籍名称
    # res = models.Author.objects.filter(name='ckk').values('book__title')
    # print(res)

    # 6.查询120电话号码的作者姓名
    # res = models.AuthorDetail.objects.filter(phone='120').values('author__name')
    # print(res)

    """基于双下划线的进阶操作 不准直接用models.条件所在的表名"""

    # 1.查询主键为1的书籍对应的出版社名称
    # res = models.Publish.objects.filter(book__pk=1).values('title')   # filter 支持正反向操作
    # print(res)

    # 2.查询主键为1的书籍对应的作者名
    # res = models.Author.objects.filter(book__pk=1).values('name')
    # print(res)

    # 3.查询ckk的电话号码
    # res = models.AuthorDetail.objects.filter(author__name='ckk').values('phone', 'author__age')
    # print(res)

    # 4.查询哈哈哈出版社出版过的书籍名称
    # res = models.Book.objects.filter(publish__title='哈哈哈出版社').values('title')
    # print(res)

    # 5.查询ckk写过的书籍名称
    # res = models.Book.objects.filter(authors__name='ckk').values('title')
    # print(res)

    # 6.查询120电话号码的作者姓名
    # res = models.Author.objects.filter(author_detail__phone=120).values('name')
    # print(res)

    # 查询主键为1的书籍对应的作者的电话号码 Book Author AuthorDetail
    # res = models.Book.objects.filter(pk=1).values('authors__author_detail__phone')
    # print(res)
    # res = models.AuthorDetail.objects.filter(author__book__pk=1).values('phone')
    # print(res)

    """F与Q查询"""
    # F查询：查询条件也来于表中其他字段对应的值 不是人为指定
    # 1.查询库存数大于卖出数的书籍
    # from django.db.models import F
    # res = models.Book.objects.filter(ku_cun__gt=F('mai_chu'))
    # print(res)

    # 2.将所有书籍的价格提升1000块
    # res = models.Book.objects.update(price=F('price') + 1000)
    # print(res)

    # 3.针对字符类型的数据批量操作 需要借助更多模块
    # 将所有的书籍名称后面添加爆款两字
    # from django.db.models.functions import Concat
    # from django.db.models import Value
    # res = models.Book.objects.update(title=Concat(F('title'), Value('新款')))

    # Q查询：能够改变多个条件的链接关系
    from django.db.models import Q

    # res = models.Book.objects.filter(pk=1, publish_id=1)  # 逗号隔开多个条件 默认and关系
    # res = models.Book.objects.filter(Q(pk=1) | Q(publish_id=1))
    # res = models.Book.objects.filter(~Q(pk=1) | Q(publish_id=1))  # ~ 取反
    # res = models.Book.objects.filter(Q(pk=1) | ~Q(publish_id=1))
    # print(res)    # """Q还可以查询条件的左边是字符串的形式 从而可以自定义查询条件"""
    # q_obj = Q()
    # q_obj.connector = 'or'
    # q_obj.children.append(['pk', 1])
    # q_obj.children.append(['publish_id', 1])
    # res = models.Book.objects.filter(q_obj)
    # print(res)

    # 聚合查询: max min count sum avg
    # 一般情况下聚合函数不会单独使用 都是结合分组一起使用的

    # 1.统计最贵的书的价格
    # from django.db.models import Max, Min, Count, Sum, Avg
    #
    # res = models.Book.objects.aggregate(Max('price'), Min('price'), Count('price'), Sum('price'), Avg('price'))
    # print(res)

    # 分组查询
    # 1.统计每一本书的作者个数
    from django.db.models import Max, Min, Count, Sum, Avg

    # res = models.Book.objects.annotate(author_num=Count('authors__pk')).values('author_num', 'title')
    # print(res)

    # 2.统计出每个出版社卖的最便宜的书的价格
    # res = models.Publish.objects.annotate(min_price=Min('book__price')).values('title', 'min_price')
    # print(res)

    # 3.统计不止一个作者的图书
    # res = models.Book.objects.annotate(author_num=Count('authors__pk')).filter(author_num__gt=1)
    # print(res)
    # 4.查询各个作者出的书的总价格
    # res = models.Author.objects.annotate(sum_price=Sum('book__price')).values('name', 'sum_price')
    # print(res)
    # res = models.Book.objects.values('publish_id').annotate(book_num=Count('pk')).values('book_num', 'publish_id')
    # print(res)

    # res = models.BookUser.objects.all()
    # print(res)
    # from django.db import transaction
    # with transaction.atomic():
    #     # 执行多条ORM语句(同属于一个事务)
    #     pass

    from app02 import models
    models.App02Book.objects.all().delete()